/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 * cambios
 */
package conexion;

/**
 *
 * @author Angel
 */

import java.sql.*;
import java.io.*;
import java.util.LinkedList;

public class DB {
    
     String dbURL = "jdbc:mysql://localhost/Fantasy";
     String dbDriver = "com.mysql.jdbc.Driver";
     String dbUser = "root";
     String dbPass = "admin";
    private Connection dbCon;
    
    private Statement SqlStatement;
    
    private Statement statement;
    private ResultSet rs;
    
    public DB(){
        super();
    }
    
    
    
    
    public boolean connect() {
        try{
            Class.forName(dbDriver).newInstance();
            dbCon = DriverManager.getConnection(dbURL, dbUser, dbPass); 
            return true;
        }catch(Exception e){
            return false;
        }   
    }
    
    public boolean disconnect(){
        this.dbCon = null;
        return true;
    }
    
    public ResultSet seleccionar(String campos, String tabla, String condicion) throws SQLException{
        statement = dbCon.createStatement();
        String Query = "SELECT "+campos + " FROM " + tabla + " WHERE " + condicion;
        rs = null;
        System.out.println(Query);
        rs = statement.executeQuery(Query);
        
        return rs;
    }
    
     public int actualizar(String campo, String valor, String tabla, String condicion) throws SQLException{
        statement = dbCon.createStatement();
        String Query = "UPDATE "+tabla + " SET " + campo + " = " + valor + " WHERE " + condicion;
        //rs = null;
        System.out.println(Query);
        int update = statement.executeUpdate(Query);
        
        return update;
                
        //UPDATE TABLA SET CAMPOS = VALOR WHERE ID_LEAGUE = NO;
    }

     public int actualizar(String campo, boolean valor, String tabla, String condicion) throws SQLException{
        statement = dbCon.createStatement();
        String Query = "UPDATE "+tabla + " SET " + campo + " = " + valor + " WHERE " + condicion;
        //rs = null;
        System.out.println(Query);
        int update = statement.executeUpdate(Query);
        
        return update;
                
        //UPDATE TABLA SET CAMPOS = VALOR WHERE ID_LEAGUE = NO;
    }
     
     public void eliminar(String tabla, String condicion) throws SQLException{
        statement = dbCon.createStatement();
        String Query = "DELETE FROM " + tabla + " WHERE " + condicion;
        SqlStatement=dbCon.createStatement();
        SqlStatement.executeUpdate(Query);
        SqlStatement.close();               
        
    }         

     public void crearLiga(String values)throws SQLException{
         statement = dbCon.createStatement();
         String Query = "INSERT INTO LEAGUE(name_league, type_league, max_league, id_country_league) VALUES ("+ values +");";
         SqlStatement=dbCon.createStatement();
         SqlStatement.executeUpdate(Query);
         SqlStatement.close();
     }
     public void crearTemplate(String values)throws SQLException{
         statement = dbCon.createStatement();
         String Query = "INSERT INTO TEMPLATE(name_template, money_template, email_user_template) VALUES ("+ values +");";
         SqlStatement=dbCon.createStatement();
         SqlStatement.executeUpdate(Query);
         SqlStatement.close();
     }
     public void crearSquad(String values)throws SQLException{
         statement = dbCon.createStatement();
         String Query = "INSERT INTO SQUAD(id_template_squad, id_player_squad) VALUES ("+ values +");";
         SqlStatement=dbCon.createStatement();
         SqlStatement.executeUpdate(Query);
         SqlStatement.close();
     }
     public void crearEquipo(String values)throws SQLException{
         statement = dbCon.createStatement();
         String Query = "INSERT INTO TEAM(name_team, year_team , address_team, ranking_team,id_stadium_team) VALUES ("+ values +");";                                 
         SqlStatement=dbCon.createStatement();
         SqlStatement.executeUpdate(Query);
         SqlStatement.close();
     }
     
     public void crearJugador(String values)throws SQLException{
         statement = dbCon.createStatement();
         String Query = "INSERT INTO Player(name_player, age_player, price_player, id_status_player, id_position_player) VALUES ("+ values +");";
         SqlStatement=dbCon.createStatement();
         SqlStatement.executeUpdate(Query);
         SqlStatement.close();
     }
     
     public int obtenerIdPais(String country) throws SQLException{
         int id = 0;
         statement = dbCon.createStatement();
         String Query = "SELECT * FROM Country WHERE name_country = \'"+ country +"\';";
         rs = null;
         rs = statement.executeQuery(Query);
         if(rs.first()){
             id = rs.getInt("id_country");
         }
         return id;
     }
       public int obtenerIdtempla(String country) throws SQLException{
         int id = 0;
         statement = dbCon.createStatement();
         String Query = "SELECT * FROM template where email_user_template  = \'"+ country +"\';";
         rs = null;
         rs = statement.executeQuery(Query);
         if(rs.first()){
             id = rs.getInt("id_template");
         }
         return id;
     }
     
     public int obtenerIdEstado(String status) throws SQLException{
         int id = 0;
         statement = dbCon.createStatement();
         String Query = "SELECT * FROM Status WHERE name_status = \'"+ status +"\';";
         rs = null;
         rs = statement.executeQuery(Query);
         if(rs.first()){
             id = rs.getInt("id_status");
         }
         return id;
     }
     
     public int obtenerIdPosicion(String position) throws SQLException{
         int id = 0;
         statement = dbCon.createStatement();
         String Query = "SELECT * FROM Position WHERE name_position = \'"+ position +"\';";
         rs = null;
         rs = statement.executeQuery(Query);
         if(rs.first()){
             id = rs.getInt("id_position");
         }
         return id;
     }
     
    public int insertar(String campo, String valor, String tabla) throws SQLException{
            statement = dbCon.createStatement();
            String Query = "INSERT INTO  "+ tabla +"("+campo+") VALUES (" + valor + ")";
            int insertar = statement.executeUpdate(Query);
            return insertar;
        }
     public int obtenerIdEstadio(String nombre) throws SQLException{
         int id = 0;
         statement = dbCon.createStatement();
         String Query = "SELECT * FROM stadium WHERE name_stadium = \'"+ nombre +"\';";
         rs = null;
         rs = statement.executeQuery(Query);
         if(rs.first()){
             id = rs.getInt("id_stadium"); 
             
         }
         System.out.println(id);
         return id;
     }
}
